if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_VouchersDetail]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_VouchersDetail]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_VouchersDetail
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, 
                      dbo.Voucher.SenderID, Accounts_1.AccountTitle AS SenderName, dbo.Voucher.ReceiverID, Accounts_2.AccountTitle AS ReceiverName, 
                      dbo.Voucher.StationFromID, dbo.Station.StationName AS StationFromName, dbo.Station.StationNameUrdu AS StationFromNameUrdu, 
                      dbo.Voucher.StationToID, Station_1.StationName AS StationToName, Station_1.StationNameUrdu AS StationToNameUrdu, dbo.Voucher.VehicleID, 
                      Accounts_3.AccountTitle AS VehicleName, dbo.Voucher.BillityNo, dbo.Voucher.TotalAmount, dbo.Voucher.Loading, dbo.Voucher.GodownIn, 
                      dbo.Voucher.MiscIn, dbo.Voucher.GrossAmount, dbo.Voucher.UnLoading, dbo.Voucher.GodownOut, dbo.Voucher.MiscOut, dbo.Voucher.NetAmount, 
                      dbo.Voucher.Expense, dbo.Voucher.Discount, ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.Accounts.ParentAccount, dbo.VoucherDetails.DrOrCr, dbo.VoucherDetails.Amount, 
                      dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.Remarks, dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, 
                      dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, 
                      dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Notes, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, 
                      dbo.VoucherDetails.Weight, dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Weight AS InWeight, 0 AS OutWeight
FROM         dbo.Accounts Accounts_3 RIGHT OUTER JOIN
                      dbo.Voucher ON Accounts_3.AccountNo = dbo.Voucher.VehicleID LEFT OUTER JOIN
                      dbo.Station Station_1 ON dbo.Voucher.StationToID = Station_1.StationID LEFT OUTER JOIN
                      dbo.Station ON dbo.Voucher.StationFromID = dbo.Station.StationID LEFT OUTER JOIN
                      dbo.Accounts Accounts_2 ON dbo.Voucher.ReceiverID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Accounts Accounts_1 ON dbo.Voucher.SenderID = Accounts_1.AccountNo RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, 
                      dbo.Voucher.SenderID, Accounts_1.AccountTitle AS SenderName, dbo.Voucher.ReceiverID, Accounts_2.AccountTitle AS ReceiverName, 
                      dbo.Voucher.StationFromID, dbo.Station.StationName AS StationFromName, dbo.Station.StationNameUrdu AS StationFromNameUrdu, 
                      dbo.Voucher.StationToID, Station_1.StationName AS StationToName, Station_1.StationNameUrdu AS StationToNameUrdu, dbo.Voucher.VehicleID, 
                      Accounts_3.AccountTitle AS VehicleName, dbo.Voucher.BillityNo, dbo.Voucher.TotalAmount, dbo.Voucher.Loading, dbo.Voucher.GodownIn, 
                      dbo.Voucher.MiscIn, dbo.Voucher.GrossAmount, dbo.Voucher.UnLoading, dbo.Voucher.GodownOut, dbo.Voucher.MiscOut, dbo.Voucher.NetAmount, 
                      dbo.Voucher.Expense, dbo.Voucher.Discount, ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.Accounts.ParentAccount, dbo.VoucherDetails.DrOrCr, dbo.VoucherDetails.Amount, 
                      0 AS Debit, dbo.VoucherDetails.Amount AS Credit, dbo.VoucherDetails.Remarks, dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, 
                      dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, 
                      dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Notes, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, 
                      dbo.VoucherDetails.Weight, 0 AS InQty, dbo.VoucherDetails.Qty AS OutQty, 0 AS InWeight, dbo.VoucherDetails.Weight AS OutWeight
FROM         dbo.Accounts Accounts_3 RIGHT OUTER JOIN
                      dbo.Voucher ON Accounts_3.AccountNo = dbo.Voucher.VehicleID LEFT OUTER JOIN
                      dbo.Station Station_1 ON dbo.Voucher.StationToID = Station_1.StationID LEFT OUTER JOIN
                      dbo.Station ON dbo.Voucher.StationFromID = dbo.Station.StationID LEFT OUTER JOIN
                      dbo.Accounts Accounts_2 ON dbo.Voucher.ReceiverID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Accounts Accounts_1 ON dbo.Voucher.SenderID = Accounts_1.AccountNo RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Cr')

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_BillityWiseStock]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_BillityWiseStock]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_BillityWiseStock
AS
SELECT     TOP 100 PERCENT dbo.vw_Items.GroupID, dbo.vw_Items.GroupName, dbo.vw_Items.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, 
                      'No' AS Unit, ISNULL(Stock.BillityNo, '.') AS BillityNo, Stock.ReceiverID, Stock.ReceiverName, ISNULL(SUM(Stock.InQty), 0) AS InQty, 
                      ISNULL(SUM(Stock.OutQty), 0) AS OutQty, ISNULL(SUM(Stock.EndQty), 0) AS EndQty, ISNULL(SUM(Stock.InWeight), 0) AS InWeight, 
                      ISNULL(SUM(Stock.OutWeight), 0) AS OutWeight, ISNULL(SUM(Stock.EndWeight), 0) AS EndWeight
FROM         (SELECT     ItemID, BillityNo, ReceiverID, ReceiverName, SUM(InQty) AS InQty, SUM(OutQty) AS OutQty, SUM(InQty) - SUM(OutQty) AS EndQty, 
                                              SUM(InWeight) AS InWeight, SUM(OutWeight) AS OutWeight, SUM(InWeight) - SUM(OutWeight) AS EndWeight
                       FROM          vw_VouchersDetail
                       WHERE      SessionID = 1 AND VOUCHERNO LIKE 'BR-%' OR
                                              VOUCHERNO LIKE 'BS-%' AND ItemID IS NOT NULL
                       GROUP BY ItemID, BillityNo, ReceiverID, ReceiverName) Stock LEFT OUTER JOIN
                      dbo.vw_Items ON Stock.ItemID = dbo.vw_Items.ItemID
WHERE     (0 = 0)
GROUP BY dbo.vw_Items.GroupID, dbo.vw_Items.GroupName, dbo.vw_Items.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, Stock.BillityNo, 
                      Stock.ReceiverID, Stock.ReceiverName
HAVING      (ISNULL(SUM(Stock.EndQty), 0) <> 0)
ORDER BY dbo.vw_Items.ItemName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO